SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


IF OBJECT_ID('dbo.[Account]', N'U') IS NOT NULL
DROP TABLE [dbo].[Account]
GO

/****** Object:  Table [dbo].[Account]    Script Date: 4/29/2014 2:29:20 PM ******/
CREATE TABLE [dbo].[Account](
	[Id] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](256) NOT NULL,
	[AccountTypeValue] [char](2) NOT NULL,
	[AccountNumber] [varchar](32) NOT NULL,
	[IndustryTypeValue] [char](2) NULL,
	[BillingStreet] [nvarchar](128) NULL,
	[BillingCity] [nvarchar](128) NULL,
	[BillingState] [nvarchar](128) NULL,
	[BillingPostalCode] [varchar](16) NULL,
	[BillingCountry] [nvarchar](128) NULL,
	[BillingLatitude] [decimal](9,2) NULL,
	[BillingLongitude] [decimal](9,2) NULL,
	[ShippingStreet] [nvarchar](128) NULL,
	[ShippingCity] [nvarchar](128) NULL,
	[ShippingState] [nvarchar](128) NULL,
	[ShippingPostalCode] [varchar](16) NULL,
	[ShippingCountry] [nvarchar](128) NULL,
	[ShippingLatitude] [decimal](9,2) NULL,
	[ShippingLongitude] [decimal](9,2) NULL,
	[Phone] [varchar](16) NULL,
	[Mobile] [varchar](16) NULL,
	[Fax] [varchar](16) NULL,
	[Website] [varchar](128) NULL,
	[Email] [varchar](128) NULL,
	[Description] [nvarchar](2048) NULL,
	[ImageData] [varchar](max) NULL,
	[RevisionNumber] [int] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[CreatedOn] [datetime] NULL,
	[ModifiedBy] [uniqueidentifier] NULL,
	[ModifiedOn] [datetime] NULL,
CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED 
	([Id] ASC)
	WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
GO


IF OBJECT_ID('dbo.[Account_Log]', N'U') IS NOT NULL
DROP TABLE [dbo].[Account_Log]
GO

/****** Object:  Table [dbo].[Account_Log]    Script Date: 4/29/2014 2:29:20 PM ******/

CREATE TABLE [dbo].[Account_Log](
	[Sr] [bigint] IDENTITY(1,1) NOT NULL,
	[Id] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](256) NOT NULL,
	[AccountTypeValue] [char](2) NOT NULL,
	[AccountNumber] [varchar](32) NOT NULL,
	[IndustryTypeValue] [char](2) NULL,
	[BillingStreet] [nvarchar](128) NULL,
	[BillingCity] [nvarchar](128) NULL,
	[BillingState] [nvarchar](128) NULL,
	[BillingPostalCode] [varchar](16) NULL,
	[BillingCountry] [nvarchar](128) NULL,
	[BillingLatitude] [decimal](9,2) NULL,
	[BillingLongitude] [decimal](9,2) NULL,
	[ShippingStreet] [nvarchar](128) NULL,
	[ShippingCity] [nvarchar](128) NULL,
	[ShippingState] [nvarchar](128) NULL,
	[ShippingPostalCode] [varchar](16) NULL,
	[ShippingCountry] [nvarchar](128) NULL,
	[ShippingLatitude] [decimal](9,2) NULL,
	[ShippingLongitude] [decimal](9,2) NULL,
	[Phone] [varchar](16) NULL,
	[Mobile] [varchar](16) NULL,
	[Fax] [varchar](16) NULL,
	[Website] [varchar](128) NULL,
	[Email] [varchar](128) NULL,
	[Description] [nvarchar](2048) NULL,
	[ImageData] [varchar](max) NULL,
	[RevisionNumber] [int] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[CreatedOn] [datetime] NULL,
	[ModifiedBy] [uniqueidentifier] NULL,
	[ModifiedOn] [datetime] NULL,
CONSTRAINT [PK_Account_Log] PRIMARY KEY CLUSTERED 
	([Sr] ASC)
	WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO


IF OBJECT_ID('dbo.[AccountView]', 'V') IS NOT NULL
DROP VIEW [dbo].[AccountView]
GO

/****** Object:  View [dbo].[AccountView]    Script Date: 4/29/2014 2:29:20 PM ******/
CREATE VIEW [dbo].[AccountView]
AS
	SELECT
	A.[Id]
	,A.[Name]
	,A.[AccountTypeValue]
	,A.[AccountNumber]
	,A.[IndustryTypeValue]
	,A.[BillingStreet]
	,A.[BillingCity]
	,A.[BillingState]
	,A.[BillingPostalCode]
	,A.[BillingCountry]
	,A.[BillingLatitude]
	,A.[BillingLongitude]
	,A.[ShippingStreet]
	,A.[ShippingCity]
	,A.[ShippingState]
	,A.[ShippingPostalCode]
	,A.[ShippingCountry]
	,A.[ShippingLatitude]
	,A.[ShippingLongitude]
	,A.[Phone]
	,A.[Mobile]
	,A.[Fax]
	,A.[Website]
	,A.[Email]
	,A.[Description]
	,A.[ImageData]
	,A.[RevisionNumber]
	,A.[CreatedBy]
	,CM.[Name] AS CreatedByName
	,A.[CreatedOn]
	,A.[ModifiedBy]
	,MM.[Name] AS ModifiedByName
	,A.[ModifiedOn]
	From Account A
	LEFT JOIN Member CM ON A.[CreatedBy] = CM.[Id]
	LEFT JOIN Member MM ON A.[ModifiedBy] = MM.[Id]

GO
SET ANSI_PADDING OFF 
GO


IF OBJECT_ID('dbo.[usp_AccountInsert]', N'P') IS NOT NULL
DROP PROCEDURE[dbo].[usp_AccountInsert]
GO

/****** Object:  StoredProcedure [dbo].[usp_AccountInsert]    Script Date: 4/29/2014 2:29:20 PM ******/
CREATE PROC [dbo].[usp_AccountInsert]
	@Id  [uniqueidentifier]
	,@Name [nvarchar](256)
	,@AccountTypeValue [char](2) 
	,@AccountNumber [varchar](32) 
	,@IndustryTypeValue [char](2)  = NULL
	,@BillingStreet [nvarchar](128)  = NULL
	,@BillingCity [nvarchar](128)  = NULL
	,@BillingState [nvarchar](128)  = NULL
	,@BillingPostalCode [varchar](16)  = NULL
	,@BillingCountry [nvarchar](128)  = NULL
	,@BillingLatitude [decimal](9,2)  = NULL
	,@BillingLongitude [decimal](9,2)  = NULL
	,@ShippingStreet [nvarchar](128)  = NULL
	,@ShippingCity [nvarchar](128)  = NULL
	,@ShippingState [nvarchar](128)  = NULL
	,@ShippingPostalCode [varchar](16)  = NULL
	,@ShippingCountry [nvarchar](128)  = NULL
	,@ShippingLatitude [decimal](9,2)  = NULL
	,@ShippingLongitude [decimal](9,2)  = NULL
	,@Phone [varchar](16)  = NULL
	,@Mobile [varchar](16)  = NULL
	,@Fax [varchar](16)  = NULL
	,@Website [varchar](128)  = NULL
	,@Email [varchar](128)  = NULL
	,@Description [nvarchar](2048)
	,@ImageData [varchar](max)
	,@UserID  [uniqueidentifier]

AS
	SET NOCOUNT ON 
	SET XACT_ABORT ON

	BEGIN TRAN

	INSERT INTO [dbo].[Account] ([Id], [Name], [AccountTypeValue], [AccountNumber], [IndustryTypeValue], [BillingStreet], [BillingCity], [BillingState], [BillingPostalCode], [BillingCountry], [BillingLatitude], [BillingLongitude], [ShippingStreet], [ShippingCity], [ShippingState], [ShippingPostalCode], [ShippingCountry], [ShippingLatitude], [ShippingLongitude], [Phone], [Mobile], [Fax], [Website], [Email], [Description], [ImageData], [RevisionNumber], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn])
	SELECT @Id, @Name, @AccountTypeValue, @AccountNumber, @IndustryTypeValue, @BillingStreet, @BillingCity, @BillingState, @BillingPostalCode, @BillingCountry, @BillingLatitude, @BillingLongitude, @ShippingStreet, @ShippingCity, @ShippingState, @ShippingPostalCode, @ShippingCountry, @ShippingLatitude, @ShippingLongitude, @Phone, @Mobile, @Fax, @Website, @Email, @Description, @ImageData, 0, @UserId, GETDATE(), NULL, NULL

	INSERT INTO [dbo].[Account_log] ([Id], [Name], [AccountTypeValue], [AccountNumber], [IndustryTypeValue], [BillingStreet], [BillingCity], [BillingState], [BillingPostalCode], [BillingCountry], [BillingLatitude], [BillingLongitude], [ShippingStreet], [ShippingCity], [ShippingState], [ShippingPostalCode], [ShippingCountry], [ShippingLatitude], [ShippingLongitude], [Phone], [Mobile], [Fax], [Website], [Email], [Description], [ImageData], [RevisionNumber], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn])
	SELECT
	A.[Id]
	,A.[Name]
	,A.[AccountTypeValue]
	,A.[AccountNumber]
	,A.[IndustryTypeValue]
	,A.[BillingStreet]
	,A.[BillingCity]
	,A.[BillingState]
	,A.[BillingPostalCode]
	,A.[BillingCountry]
	,A.[BillingLatitude]
	,A.[BillingLongitude]
	,A.[ShippingStreet]
	,A.[ShippingCity]
	,A.[ShippingState]
	,A.[ShippingPostalCode]
	,A.[ShippingCountry]
	,A.[ShippingLatitude]
	,A.[ShippingLongitude]
	,A.[Phone]
	,A.[Mobile]
	,A.[Fax]
	,A.[Website]
	,A.[Email]
	,A.[Description]
	,A.[ImageData]
	,A.[RevisionNumber]
	,A.[CreatedBy]
	,A.[CreatedOn]
	,A.[ModifiedBy]
	,A.[ModifiedOn]
	From Account A

	WHERE A.[Id] = @Id


	COMMIT;

	SELECT
	A.[Id]
	,A.[Name]
	,A.[AccountTypeValue]
	,A.[AccountNumber]
	,A.[IndustryTypeValue]
	,A.[BillingStreet]
	,A.[BillingCity]
	,A.[BillingState]
	,A.[BillingPostalCode]
	,A.[BillingCountry]
	,A.[BillingLatitude]
	,A.[BillingLongitude]
	,A.[ShippingStreet]
	,A.[ShippingCity]
	,A.[ShippingState]
	,A.[ShippingPostalCode]
	,A.[ShippingCountry]
	,A.[ShippingLatitude]
	,A.[ShippingLongitude]
	,A.[Phone]
	,A.[Mobile]
	,A.[Fax]
	,A.[Website]
	,A.[Email]
	,A.[Description]
	,A.[ImageData]
	,A.[RevisionNumber]
	,A.[CreatedBy]
	,A.[CreatedOn]
	,A.[ModifiedBy]
	,A.[ModifiedOn]
	From Account A
	WHERE A.[Id] = @Id

GO


IF OBJECT_ID('dbo.[usp_AccountUpdate]', N'P') IS NOT NULL
DROP PROCEDURE[dbo].[usp_AccountUpdate]
GO

/****** Object:  StoredProcedure [dbo].[usp_AccountUpdate]    Script Date: 4/29/2014 2:29:20 PM ******/
CREATE PROC [dbo].[usp_AccountUpdate]
	@Id  [uniqueidentifier]
	,@Name [nvarchar](256)
	,@AccountTypeValue [char](2) 
	,@AccountNumber [varchar](32) 
	,@IndustryTypeValue [char](2)  = NULL
	,@BillingStreet [nvarchar](128)  = NULL
	,@BillingCity [nvarchar](128)  = NULL
	,@BillingState [nvarchar](128)  = NULL
	,@BillingPostalCode [varchar](16)  = NULL
	,@BillingCountry [nvarchar](128)  = NULL
	,@BillingLatitude [decimal](9,2)  = NULL
	,@BillingLongitude [decimal](9,2)  = NULL
	,@ShippingStreet [nvarchar](128)  = NULL
	,@ShippingCity [nvarchar](128)  = NULL
	,@ShippingState [nvarchar](128)  = NULL
	,@ShippingPostalCode [varchar](16)  = NULL
	,@ShippingCountry [nvarchar](128)  = NULL
	,@ShippingLatitude [decimal](9,2)  = NULL
	,@ShippingLongitude [decimal](9,2)  = NULL
	,@Phone [varchar](16)  = NULL
	,@Mobile [varchar](16)  = NULL
	,@Fax [varchar](16)  = NULL
	,@Website [varchar](128)  = NULL
	,@Email [varchar](128)  = NULL
	,@Description [nvarchar](2048)
	,@ImageData [varchar](max)
	,@UserID  [uniqueidentifier]

AS
	SET NOCOUNT ON 
	SET XACT_ABORT ON

	BEGIN TRAN

	UPDATE [dbo].[Account]
	SET [Id] = @Id, [Name] = @Name, [AccountTypeValue] = @AccountTypeValue, [AccountNumber] = @AccountNumber, [IndustryTypeValue] = @IndustryTypeValue, [BillingStreet] = @BillingStreet, [BillingCity] = @BillingCity, [BillingState] = @BillingState, [BillingPostalCode] = @BillingPostalCode, [BillingCountry] = @BillingCountry, [BillingLatitude] = @BillingLatitude, [BillingLongitude] = @BillingLongitude, [ShippingStreet] = @ShippingStreet, [ShippingCity] = @ShippingCity, [ShippingState] = @ShippingState, [ShippingPostalCode] = @ShippingPostalCode, [ShippingCountry] = @ShippingCountry, [ShippingLatitude] = @ShippingLatitude, [ShippingLongitude] = @ShippingLongitude, [Phone] = @Phone, [Mobile] = @Mobile, [Fax] = @Fax, [Website] = @Website, [Email] = @Email, [Description] = @Description, [ImageData] = @ImageData, [RevisionNumber] = [RevisionNumber] + 1, [ModifiedBy] = @UserId, [ModifiedOn] = GETDATE()	WHERE [Id] = @Id


	INSERT INTO [dbo].[Account_log] ([Id], [Name], [AccountTypeValue], [AccountNumber], [IndustryTypeValue], [BillingStreet], [BillingCity], [BillingState], [BillingPostalCode], [BillingCountry], [BillingLatitude], [BillingLongitude], [ShippingStreet], [ShippingCity], [ShippingState], [ShippingPostalCode], [ShippingCountry], [ShippingLatitude], [ShippingLongitude], [Phone], [Mobile], [Fax], [Website], [Email], [Description], [ImageData], [RevisionNumber], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn])
	SELECT
	A.[Id]
	,A.[Name]
	,A.[AccountTypeValue]
	,A.[AccountNumber]
	,A.[IndustryTypeValue]
	,A.[BillingStreet]
	,A.[BillingCity]
	,A.[BillingState]
	,A.[BillingPostalCode]
	,A.[BillingCountry]
	,A.[BillingLatitude]
	,A.[BillingLongitude]
	,A.[ShippingStreet]
	,A.[ShippingCity]
	,A.[ShippingState]
	,A.[ShippingPostalCode]
	,A.[ShippingCountry]
	,A.[ShippingLatitude]
	,A.[ShippingLongitude]
	,A.[Phone]
	,A.[Mobile]
	,A.[Fax]
	,A.[Website]
	,A.[Email]
	,A.[Description]
	,A.[ImageData]
	,A.[RevisionNumber]
	,A.[CreatedBy]
	,A.[CreatedOn]
	,A.[ModifiedBy]
	,A.[ModifiedOn]
	From Account A

	WHERE A.[Id] = @Id


	COMMIT;

	SELECT
	A.[Id]
	,A.[Name]
	,A.[AccountTypeValue]
	,A.[AccountNumber]
	,A.[IndustryTypeValue]
	,A.[BillingStreet]
	,A.[BillingCity]
	,A.[BillingState]
	,A.[BillingPostalCode]
	,A.[BillingCountry]
	,A.[BillingLatitude]
	,A.[BillingLongitude]
	,A.[ShippingStreet]
	,A.[ShippingCity]
	,A.[ShippingState]
	,A.[ShippingPostalCode]
	,A.[ShippingCountry]
	,A.[ShippingLatitude]
	,A.[ShippingLongitude]
	,A.[Phone]
	,A.[Mobile]
	,A.[Fax]
	,A.[Website]
	,A.[Email]
	,A.[Description]
	,A.[ImageData]
	,A.[RevisionNumber]
	,A.[CreatedBy]
	,A.[CreatedOn]
	,A.[ModifiedBy]
	,A.[ModifiedOn]
	From Account A
	WHERE A.[Id] = @Id

GO


IF OBJECT_ID('dbo.[usp_AccountDelete]', N'P') IS NOT NULL
DROP PROCEDURE[dbo].[usp_AccountDelete]
GO

/****** Object:  StoredProcedure [dbo].[usp_AccountDelete]    Script Date: 4/29/2014 2:29:20 PM ******/
CREATE PROC [dbo].[usp_AccountDelete]
	@Id uniqueidentifier
AS
SET NOCOUNT ON
SET XACT_ABORT ON

BEGIN TRAN
	DELETE FROM [dbo].[Account] WHERE  [Id] = @Id
COMMIT;
GO

